package com.googlecode.maratische.google.dao.impl;

import java.io.BufferedReader;
import java.io.File;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.List;

import org.apache.log4j.Logger;

import com.googlecode.maratische.google.BaseException;
import com.googlecode.maratische.google.dao.ItemDao;
import com.googlecode.maratische.google.model.Constants;
import com.googlecode.maratische.google.model.Item;

public class ItemDaoImpl extends BaseDaoImpl<Item> implements ItemDao {

	public ItemDaoImpl(Connection conn) {
		super(conn);
	}
	
	
	protected static Logger getLogger() {
		return Logger.getLogger(ItemDaoImpl.class.getName());
	}

	public String getLocalMediaFolder(String itemId) {
		String result = getRootMediaFolder();
		result += File.separator + itemId;
		return result;
	}


	public String getRootMediaFolder() {
		String result = null;
		result = System.getProperty("user.home");
		result += File.separator + Constants.RSS_READER_FOLDER_NAME;
		result += File.separator + "media";
		return result;
	}

	public List<Item> listAllLabeled() throws SQLException, BaseException {
		return list("LABEL_ID is not NULL", "UPDATED_DATE desc");
	}
	
	@Override
	public List<Item> listLabeledByType(String type) throws SQLException, BaseException {
		return list(" LABEL_ID in (select ID from LABEL where TYPE = ? ) ", "UPDATED_DATE desc", new Object[] {type});
	}
	
	public int countLabeledByType(String type) throws SQLException, BaseException {
		/**
		 * TODO change to more fast count
		 */
		List<Item> items = list(" LABEL_ID in (select ID from LABEL where TYPE = ? ) ", null, new Object[] {type});
		if (items != null) {
			return items.size();
		}
		return 0;
	}

	public int countAllLabeled() throws SQLException, BaseException {
		/**
		 * TODO change to more fast count
		 */
		List<Item> items = list("LABEL_ID is not NULL", null);
		if (items != null) {
			return items.size();
		}
		return 0;
	}
	
	public List<Item> findReadedForErase(Date date) throws SQLException, BaseException {
		return list("ITEM_READ = 1 and READED_DATE < ? ",null, new Object[] {date});
	}
	
	public List<Item> findByLink(String link) throws SQLException, BaseException {
		return list("LINK = ? ",null, new Object[] {link});
	}
	
	public List<Item> findByFeedIdUnread(Integer feedId) throws SQLException, BaseException {
		return list("FEED_ID = ? AND ITEM_READ = 0 ","UPDATED_DATE desc", new Object[] {feedId});
	}
	
	public List<Item> findByFeedId(Integer feedId) throws SQLException, BaseException {
		return list("FEED_ID = ? ","UPDATED_DATE desc", new Object[] {feedId});
	}
	
	public int countItemsByFeedIdUnread(Integer feedId) throws SQLException, BaseException {
		/**
		 * TODO change to more fast count
		 */
		List<Item> items = list("FEED_ID = ? AND ITEM_READ = 0 ",null, new Object[] {feedId});
		if (items != null) {
			return items.size();
		}
		return 0;
	}

	@Override
	public Item createByRow(ResultSet rs) throws SQLException {
		Item item = new Item();
		item.setId(rs.getInt("ID"));
		item.setRead(rs.getBoolean("ITEM_READ"));
		item.setUri(rs.getString("URI"));
		item.setLink(rs.getString("LINK"));
		item.setTitle(rs.getString("TITLE"));
		item.setFeedId(rs.getInt("FEED_ID"));
		item.setReadedDate(rs.getDate("READED_DATE"));
		item.setLabelId(rs.getInt("LABEL_ID") > 0 ? rs.getInt("LABEL_ID") : null);
		item.setUpdatedDate(rs.getTimestamp("UPDATED_DATE"));
		item.setSourceUri(rs.getString("source_uri"));
		item.setAuthor(rs.getString("author"));
		
		BufferedReader bufferedReader = null;
		try {
			bufferedReader = new BufferedReader(rs.getCharacterStream("DESCRIPTION"));
			StringBuffer sb = new StringBuffer();
			String temp = null;
			while ( (temp = bufferedReader.readLine()) != null) {
				sb.append(temp);
			}
			item.setDescription(sb.toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			logger.error("bufferedReader "+ bufferedReader);
			e.getStackTrace();
		}

		return item;
	}

	@Override
	protected String generateInsertSql() {
		return "insert into "+getTableName()+" (FEED_ID, URI,TITLE,LINK, DESCRIPTION, ITEM_READ, READED_DATE, LABEL_ID, UPDATED_DATE, source_uri, author) values (?,?,?,?,?,?, ?, ?, ?, ?, ?)";
	}

	@Override
	protected String generateUpdateSql() {
		return "update "+getTableName()+" set FEED_ID = ?,URI = ?,TITLE = ?, LINK = ?, DESCRIPTION = ?, ITEM_READ = ?, READED_DATE = ?, LABEL_ID = ?, UPDATED_DATE = ?, source_uri = ?, author = ? "+
		" where ID = ? ";
	}

	@Override
	protected String getTableName() {
		return "ITEM";
	}

	@Override
	protected PreparedStatement putInsertPrepareStatement(PreparedStatement pStat, Item status) throws SQLException {
		pStat.setInt(1, status.getFeedId());
		pStat.setString(2, status.getUri());
		pStat.setString(3, status.getTitle());
		pStat.setString(4, status.getLink());
		if (status.getDescription() != null) {
			pStat.setClob(5, new StringReader(status.getDescription()));
		} else {
			pStat.setClob(5, new StringReader(""));
		}
//		pStat.setInt(6, status.isRead() ? 1 : 0);
		pStat.setBoolean(6, status.isRead());
		pStat.setDate(7, status.getReadedDate());
		if (status.getLabelId() != null) {
			pStat.setInt(8, status.getLabelId());
		} else {
			pStat.setNull(8, Types.INTEGER);
		}
		if (status.getUpdatedDate() != null) {
			pStat.setTimestamp(9, new Timestamp(status.getUpdatedDate().getTime()));
		} else {
			pStat.setNull(9, Types.TIMESTAMP);
		}
		pStat.setString(10, status.getSourceUri());
		pStat.setString(11, status.getAuthor());
		return pStat;
	}
	
	@Override
	protected PreparedStatement putUpdatePrepareStatement(PreparedStatement pStat, Item status) throws SQLException {
		pStat = putInsertPrepareStatement(pStat, status);
		pStat.setInt(12, status.getId());
		return pStat;
	}


	/**
	 * TODO надо изменить на один быстрый sql запросы типа Update
	 * @throws BaseException 
	 */
	@Override
	public void removeReadedItem(Integer feedId, List<Integer> existIds) throws SQLException, BaseException {
		StringBuffer sql = new StringBuffer();
		for (Integer id : existIds) {
			if (sql.length()>0) {
				sql.append(",");
			}
			sql.append(id);
		}
		if (sql.length()>0) {
			sql.insert(0, " and NOT ID in (").append(") ");
		}
		List<Item> items =  list("FEED_ID = ?  AND ITEM_READ = 0 "+sql.toString(), null, new Object[] {feedId});
		for (Item item : items) {
//			System.out.println(item.getTitle());
			item.setRead(true);
			this.save(item);
		}
		// TODO Auto-generated method stub
		
	}
	

}
